1 using System;
2 using System.Collections.Generic;
3 using System.ComponentModel;
4 using System.Data;
5 using System.Drawing;
6 using System.Linq;
7 using System.Text;
8 using System.Windows.Forms;
9 using System.Data.SqlClient;
10 using Excel = Microsoft.Office.Interop.Excel;
11 namespace WarehouseManagementSystem
12 {
13 public partial class frmSuppliersRecord1 : Form
14 {
15
16 DataTable dtable = new DataTable();
17 SqlConnection con = null;
18 DataSet ds = new DataSet();
19 SqlCommand cmd = null;
20 DataTable dt = new DataTable();
21 ConnectionString cs = new ConnectionString();
22 public frmSuppliersRecord1()
23 {
24 InitializeComponent();
25 }
26 public void GetData()
27 {
28 try{
29 con = new SqlConnection(cs.DBConn);
30 con.Open();
31 cmd = new SqlCommand("SELECT RTRIM(SupplierID)as [Supplier ID],RTRIM(Suppliername) as [Supplier Name],RTRIM(address) as [Address],RTRIM(city) as [City],RTRIM(ContactNo) as [Contact No.],RTRIM(ContactNo1) as [Contact No. 1],(email) as [Email],(notes) as [Notes] from Supplier order by SupplierName", con);
32 SqlDataAdapter myDA = new SqlDataAdapter(cmd);
33 DataSet myDataSet = new DataSet();
34 myDA.Fill(myDataSet, "Supplier");
35 dataGridView1.DataSource = myDataSet.Tables["Supplier"].DefaultView;
36 con.Close();
37 }
38 catch (Exception ex)
39 {
40 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
41 }
42 }
43
44 private void frmSuppliersRecord_Load(object sender, EventArgs e)
45 {
46 GetData();
47 }
48
49
50
51 private void dataGridView1_RowPostPaint(object sender, DataGridViewRowPostPaintEventArgs e)
52 {
53 string strRowNumber = (e.RowIndex + 1).ToString();
54 SizeF size = e.Graphics.MeasureString(strRowNumber, this.Font);
55 if (dataGridView1.RowHeadersWidth < Convert.ToInt32((size.Width + 20)))
56 {
57 dataGridView1.RowHeadersWidth = Convert.ToInt32((size.Width + 20));
58 }
59 Brush b = SystemBrushes.ControlText;
60 e.Graphics.DrawString(strRowNumber, this.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2));
61
62 }
63
64 private void dataGridView1_RowHeaderMouseClick(object sender, DataGridViewCellMouseEventArgs e)
65 {
66 try{
67 DataGridViewRow dr = dataGridView1.SelectedRows[0];
68 this.Hide();
69 frmSuppliers frm= new frmSuppliers();
70 frm.Show();
71 frm.txtSupplierID.Text = dr.Cells[0].Value.ToString();
72 frm.txtSupplierName.Text = dr.Cells[1].Value.ToString();
73 frm.txtAddress.Text = dr.Cells[2].Value.ToString();
74 frm.txtCity.Text = dr.Cells[3].Value.ToString();
75 frm.txtContactNo.Text = dr.Cells[4].Value.ToString();
76 frm.txtContactNo1.Text = dr.Cells[5].Value.ToString();
77 frm.txtEmail.Text = dr.Cells[6].Value.ToString();
78 frm.txtNotes.Text = dr.Cells[7].Value.ToString();
79 frm.btnUpdate.Enabled = true;
80 frm.btnDelete.Enabled = true;
81 frm.btnSave.Enabled = false;
82 frm.txtSupplierName.Focus();
83 }
84
85 catch (Exception ex)
86 {
87 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
88 }
89 }
90
91
92 private void Button3_Click(object sender, EventArgs e)
93 {
94 if (dataGridView1.DataSource == null)
95 {
96 MessageBox.Show("Sorry nothing to export into excel sheet..", "", MessageBoxButtons.OK, MessageBoxIcon.Error);
97 return;
98 }
99 int rowsTotal = 0;
100 int colsTotal = 0;
101 int I = 0;
102 int j = 0;
103 int iC = 0;
104 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.WaitCursor;
105 Excel.Application xlApp = new Excel.Application();
106
107 try
108 {
109 Excel.Workbook excelBook = xlApp.Workbooks.Add();
110 Excel.Worksheet excelWorksheet = (Excel.Worksheet)excelBook.Worksheets[1];
111 xlApp.Visible = true;
112
113 rowsTotal = dataGridView1.RowCount;
114 colsTotal = dataGridView1.Columns.Count - 1;
115 var _with1 = excelWorksheet;
116 _with1.Cells.Select();
117 _with1.Cells.Delete();
118 for (iC = 0; iC <= colsTotal; iC++)
119 {
120 _with1.Cells[1, iC + 1].Value = dataGridView1.Columns[iC].HeaderText;
121 }
122 for (I = 0; I <= rowsTotal - 1; I++)
123 {
124 for (j = 0; j <= colsTotal; j++)
125 {
126 _with1.Cells[I + 2, j + 1].value = dataGridView1.Rows[I].Cells[j].Value;
127 }
128 }
129 _with1.Rows["1:1"].Font.FontStyle = "Bold";
130 _with1.Rows["1:1"].Font.Size = 12;
131
132 _with1.Cells.Columns.AutoFit();
133 _with1.Cells.Select();
134 _with1.Cells.EntireColumn.AutoFit();
135 _with1.Cells[1, 1].Select();
136 }
137 catch (Exception ex)
138 {
139 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
140 }
141 finally
142 {
143 //RELEASE ALLOACTED RESOURCES
144 System.Windows.Forms.Cursor.Current = System.Windows.Forms.Cursors.Default;
145 xlApp = null;
146 }
147 }
148
149
150 private void txtSuppliers_TextChanged(object sender, EventArgs e)
151 {
152 try
153 {
154 con = new SqlConnection(cs.DBConn);
155 con.Open();
156 cmd = new SqlCommand("SELECT RTRIM(SupplierID)as [Supplier ID],RTRIM(Suppliername) as [Supplier Name],RTRIM(address) as [Address],RTRIM(city) as [City],RTRIM(ContactNo) as [Contact No.],RTRIM(ContactNo1) as [Contact No. 1],(email) as [Email],(notes) as [Notes] from Supplier where SupplierName like '" + txtSuppliers.Text + "%' order by SupplierName", con);
157 SqlDataAdapter myDA = new SqlDataAdapter(cmd);
158 DataSet myDataSet = new DataSet();
159 myDA.Fill(myDataSet, "Supplier");
160 dataGridView1.DataSource = myDataSet.Tables["Supplier"].DefaultView;
161 con.Close();
162 }
163 catch (Exception ex)
164 {
165 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);
166 }
167 }
168
169 private void frmSuppliersRecord_FormClosing(object sender, FormClosingEventArgs e)
170 {
171 this.Hide();
172 frmSuppliers frm = new frmSuppliers();
173 frm.Show();
174 }
175
176 private void frmSuppliersRecord_Load_1(object sender, EventArgs e)
177 {
178
179 }
180
181
182
183 }
184 }
